#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CY.GFive.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace CY.GFive.DALProviders.SqlServerProvider
{
	public class TrainProjectItemProvider : CY.GFive.Core.Providers.DALProvider.ITrainProjectItemProvider
	{
		#region Sql
		private static readonly string SqlSelectTrainProjectItem = "SELECT [Id], [ProjectCode],[ItemStartDate],[ItemConsistDate],[ItemEndDate],[Content],[StartTime],[ConsistTime],[Position],[Des] FROM [TrainProjectItem] WHERE  [Id]=@Id";

		private static readonly string SqlInsertTrainProjectItem = "INSERT INTO [TrainProjectItem]([ProjectCode],[ItemStartDate],[ItemConsistDate],[ItemEndDate],[Content],[StartTime],[ConsistTime],[Position],[Des]) values(@ProjectCode,@ItemStartDate,@ItemConsistDate,@ItemEndDate,@Content,@StartTime,@ConsistTime,@Position,@Des);SELECT SCOPE_IDENTITY();";

		private static readonly string SqlUpdateTrainProjectItem = "UPDATE [TrainProjectItem] SET [ProjectCode] = @ProjectCode,[ItemStartDate] = @ItemStartDate,[ItemConsistDate] = @ItemConsistDate,[ItemEndDate] = @ItemEndDate,[Content] = @Content,[StartTime] = @StartTime,[ConsistTime] = @ConsistTime,[Position] = @Position,[Des] = @Des WHERE  [Id]=@Id";

		private static readonly string SqlDeleteTrainProjectItem = "DELETE [TrainProjectItem] WHERE  [Id]=@Id";

		private static readonly string SqlGetAllTrainProjectItem = "SELECT [Id], [ProjectCode],[ItemStartDate],[ItemConsistDate],[ItemEndDate],[Content],[StartTime],[ConsistTime],[Position],[Des] FROM [TrainProjectItem]";
		#endregion

		#region ITrainProjectItemProvider Members
		public Core.Business.TrainProjectItem Select(Decimal id)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Decimal, id);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectTrainProjectItem);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				Core.Business.TrainProjectItem trainProjectItem = new Core.Business.TrainProjectItem();

				if (!reader.IsDBNull(0)) trainProjectItem.Id = reader.GetDecimal(0);
				if (!reader.IsDBNull(1)) trainProjectItem.ProjectCode = reader.GetString(1);
				if (!reader.IsDBNull(2)) trainProjectItem.ItemStartDate = reader.GetDateTime(2);
				if (!reader.IsDBNull(3)) trainProjectItem.ItemConsistDate = reader.GetInt32(3);
				if (!reader.IsDBNull(4)) trainProjectItem.ItemEndDate = reader.GetDateTime(4);
				if (!reader.IsDBNull(5)) trainProjectItem.Content = reader.GetString(5);
				if (!reader.IsDBNull(6)) trainProjectItem.StartTime = reader.GetString(6);
				if (!reader.IsDBNull(7)) trainProjectItem.ConsistTime = reader.GetString(7);
				if (!reader.IsDBNull(8)) trainProjectItem.Position = reader.GetString(8);
				if (!reader.IsDBNull(9)) trainProjectItem.Des = reader.GetString(9);

				return trainProjectItem;
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();

				return null;
			}
		}

		public void Insert(Core.Business.TrainProjectItem trainProjectItem)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@ProjectCode", SqlDbType.NVarChar, trainProjectItem.ProjectCode);
			sql.AddParameter("@ItemStartDate", SqlDbType.DateTime, trainProjectItem.ItemStartDate);
			sql.AddParameter("@ItemConsistDate", SqlDbType.Int, trainProjectItem.ItemConsistDate);
			sql.AddParameter("@ItemEndDate", SqlDbType.DateTime, trainProjectItem.ItemEndDate);
			sql.AddParameter("@Content", SqlDbType.NVarChar, trainProjectItem.Content);
			sql.AddParameter("@StartTime", SqlDbType.NVarChar, trainProjectItem.StartTime);
			sql.AddParameter("@ConsistTime", SqlDbType.NVarChar, trainProjectItem.ConsistTime);
			sql.AddParameter("@Position", SqlDbType.NVarChar, trainProjectItem.Position);
			sql.AddParameter("@Des", SqlDbType.NVarChar, trainProjectItem.Des);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertTrainProjectItem);

			if (reader != null && !reader.IsClosed && reader.Read())
			{

				reader.Close();
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void Update(Core.Business.TrainProjectItem trainProjectItem)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@ProjectCode", SqlDbType.NVarChar, trainProjectItem.ProjectCode);
			sql.AddParameter("@ItemStartDate", SqlDbType.DateTime, trainProjectItem.ItemStartDate);
			sql.AddParameter("@ItemConsistDate", SqlDbType.Int, trainProjectItem.ItemConsistDate);
			sql.AddParameter("@ItemEndDate", SqlDbType.DateTime, trainProjectItem.ItemEndDate);
			sql.AddParameter("@Content", SqlDbType.NVarChar, trainProjectItem.Content);
			sql.AddParameter("@StartTime", SqlDbType.NVarChar, trainProjectItem.StartTime);
			sql.AddParameter("@ConsistTime", SqlDbType.NVarChar, trainProjectItem.ConsistTime);
			sql.AddParameter("@Position", SqlDbType.NVarChar, trainProjectItem.Position);
			sql.AddParameter("@Des", SqlDbType.NVarChar, trainProjectItem.Des);
			sql.AddParameter("@Id", SqlDbType.Decimal, trainProjectItem.Id);
			sql.ExecuteSql(SqlUpdateTrainProjectItem);
		}

		public void Delete(Core.Business.TrainProjectItem trainProjectItem)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Decimal, trainProjectItem.Id);
			sql.ExecuteSql(SqlDeleteTrainProjectItem);
		}

		public IList<Core.Business.TrainProjectItem> GetAllTrainProjectItem()
		{
			IList<Core.Business.TrainProjectItem> trainProjectItemlist = new List<Core.Business.TrainProjectItem>();
			SqlServerUtility sql = new SqlServerUtility();

			SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllTrainProjectItem);

			if(reader != null)
			{
				while(reader.Read())
				{
					Core.Business.TrainProjectItem trainProjectItem = new Core.Business.TrainProjectItem();

					if (!reader.IsDBNull(0)) trainProjectItem.Id = reader.GetDecimal(0);
					if (!reader.IsDBNull(1)) trainProjectItem.ProjectCode = reader.GetString(1);
					if (!reader.IsDBNull(2)) trainProjectItem.ItemStartDate = reader.GetDateTime(2);
					if (!reader.IsDBNull(3)) trainProjectItem.ItemConsistDate = reader.GetInt32(3);
					if (!reader.IsDBNull(4)) trainProjectItem.ItemEndDate = reader.GetDateTime(4);
					if (!reader.IsDBNull(5)) trainProjectItem.Content = reader.GetString(5);
					if (!reader.IsDBNull(6)) trainProjectItem.StartTime = reader.GetString(6);
					if (!reader.IsDBNull(7)) trainProjectItem.ConsistTime = reader.GetString(7);
					if (!reader.IsDBNull(8)) trainProjectItem.Position = reader.GetString(8);
					if (!reader.IsDBNull(9)) trainProjectItem.Des = reader.GetString(9);

					trainProjectItem.MarkOld();
					trainProjectItemlist.Add(trainProjectItem);
				}
				reader.Close();
			}
			return trainProjectItemlist;
		}
		#endregion
	}
}
